{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"https://juliacomputing.com/assets/img/new/JuliaDB_logo2.svg\", width=200>\n",
    "\n",
    "# JuliaDB is an analytical database that...\n",
    "\n",
    "- **Loads multi-file datasets**\n",
    "- **Sorts data by index variables for fast filter, aggregation, sort, and join operations**\n",
    "- **Compiles queries** (it's Julia all the way down)\n",
    "- **Stores ANY data type** (again, it's Julia)\n",
    "- **Saves tables to disk for fast reloading**\n",
    "- **Uses Julia's built-in parallel computing features to fully utilize any machine or cluster**\n",
    "- **Integrates with [OnlineStats](https://github.com/joshday/OnlineStats.jl) for big (or small) data analytics**\n",
    "\n",
    "# Helpful links\n",
    "\n",
    "For additional documentation, please refer to\n",
    "\n",
    "- [JuliaDB Docs](http://juliadb.org/latest/api/index.html)\n",
    "- [OnlineStats Docs](http://joshday.github.io/OnlineStats.jl/stable/)\n",
    "\n",
    "# Getting Started\n",
    "\n",
    "In this notebook, we'll introduce **JuliaDB**'s two main data structures (`Table` and `NDSparse`) by\n",
    "\n",
    "1. Creating tables from vectors\n",
    "1. Accessing data from `Table` and `NDSparse`\n",
    "1. Loading tables from CSVs\n",
    "1. Saving tables into binary format\n",
    "1. Reloading a saved table\n",
    "1. Using selectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[36mINFO: \u001b[39m\u001b[22m\u001b[36mRecompiling stale cache file /Users/joshday/.julia/lib/v0.6/CodecZlib.ji for module CodecZlib.\n",
      "\u001b[39m\u001b[1m\u001b[36mINFO: \u001b[39m\u001b[22m\u001b[36mRecompiling stale cache file /Users/joshday/.julia/lib/v0.6/JuliaDB.ji for module JuliaDB.\n",
      "\u001b[39m"
     ]
    }
   ],
   "source": [
    "using JuliaDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating tables from Julia Vectors\n",
    "\n",
    "- First we'll make some data vectors "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Bool[false, true, false, true], ['B', 'B', 'A', 'A'], [0.1, 0.3, 0.2, 0.4])"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = [false, true, false, true]\n",
    "y = ['B', 'B', 'A', 'A']\n",
    "z = [.1, .3, .2, .4]\n",
    "x, y, z"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `Table`: Tabular Data Sorted by Primary Key(s)\n",
    "\n",
    "- We can create a table in **JuliaDB** using the `table` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 4 rows, 3 columns:\n",
       "1      2    3\n",
       "───────────────\n",
       "false  'B'  0.1\n",
       "true   'B'  0.3\n",
       "false  'A'  0.2\n",
       "true   'A'  0.4"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# x, y, z become columns of the table, with default numbering 1, 2, 3.\n",
    "\n",
    "t1 = table(x, y, z)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 4 rows, 3 columns:\n",
       "x      y    z\n",
       "───────────────\n",
       "false  'B'  0.1\n",
       "true   'B'  0.3\n",
       "false  'A'  0.2\n",
       "true   'A'  0.4"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The keyword argument `names` lets us label the columns\n",
    "\n",
    "t2 = table(x,  y, z, names = [:x, :y, :z])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting by Index Variables\n",
    "- Furthermore, we can **sort** a table by a primary key (or keys), which can be set with the keyword argument `pkey`.\n",
    "- Below, the rows of `t3` are sorted first by the values in column `x` and second by the values in column `y`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 4 rows, 3 columns:\n",
       "\u001b[1mx      \u001b[22m\u001b[1my    \u001b[22mz\n",
       "───────────────\n",
       "false  'A'  0.2\n",
       "false  'B'  0.1\n",
       "true   'A'  0.4\n",
       "true   'B'  0.3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t3 = table(x, y, z, names = [:x, :y, :z], pkey = (:x, :y))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tuple vs. NamedTuple\n",
    "\n",
    "- We can also build a `table` from a `NamedTuple` of vectors.\n",
    "- A `NamedTuple` is created with the `@NT` macro.  Values can be accessed by position or name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a[1] = \"John\"\n"
     ]
    }
   ],
   "source": [
    "# Tuple\n",
    "\n",
    "a = (\"John\", \"Doe\")\n",
    "\n",
    "@show a[1];"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b[1] = \"John\"\n",
      "b[:firstname] = \"John\"\n",
      "b.firstname = \"John\"\n"
     ]
    }
   ],
   "source": [
    "# NamedTuple\n",
    "\n",
    "b = @NT(firstname = \"John\", lastname = \"Doe\")\n",
    "\n",
    "@show b[1]\n",
    "@show b[:firstname]\n",
    "@show b.firstname;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 4 rows, 3 columns:\n",
       "\u001b[1mx      \u001b[22m\u001b[1my    \u001b[22mz\n",
       "───────────────\n",
       "false  'A'  0.2\n",
       "false  'B'  0.1\n",
       "true   'A'  0.4\n",
       "true   'B'  0.3"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Column names are taken from the NamedTuple\n",
    "\n",
    "t4 = table(@NT(x=x, y=y, z=z), pkey = [:x, :y])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `NDSparse`: N-dimensional Sparse Array with Arbitrary Indexes\n",
    "\n",
    "- Compare the following two data structures:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2×2 SparseMatrixCSC{Float64,Int64} with 4 stored entries:\n",
       "  [1, 1]  =  0.1\n",
       "  [2, 1]  =  0.3\n",
       "  [1, 2]  =  0.2\n",
       "  [2, 2]  =  0.4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# maps (tuple of integers) -> value\n",
    "\n",
    "sparse(reshape(z, 2, 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-d NDSparse with 4 values (Float64):\n",
       "1      2   │\n",
       "───────────┼────\n",
       "false  'A' │ 0.2\n",
       "false  'B' │ 0.1\n",
       "true   'A' │ 0.4\n",
       "true   'B' │ 0.3"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# maps (tuple of arbitrary index types) -> value\n",
    "\n",
    "ndsparse((x, y), z)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Like `table`, we can use `ndsparse` with NamedTuples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-d NDSparse with 4 values (Float64):\n",
       "x      y   │\n",
       "───────────┼────\n",
       "false  'A' │ 0.2\n",
       "false  'B' │ 0.1\n",
       "true   'A' │ 0.4\n",
       "true   'B' │ 0.3"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nd2 = ndsparse(@NT(x=x, y=y), z)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Accessing data from `Table` and `NDSparse`\n",
    "\n",
    "## Index into `Table`\n",
    "\n",
    "- In the last section, we created the `Table`, `t3`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 4 rows, 3 columns:\n",
       "\u001b[1mx      \u001b[22m\u001b[1my    \u001b[22mz\n",
       "───────────────\n",
       "false  'A'  0.2\n",
       "false  'B'  0.1\n",
       "true   'A'  0.4\n",
       "true   'B'  0.3"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- We can get the first row of `t3` by indexing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(x = false, y = 'A', z = 0.2)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t3[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- We can get an individual element in the row by using the column name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t3[1].z"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Index into `NDSparse`\n",
    "\n",
    "- Since `NDSparse` acts like an array, accessing data is slightly different.\n",
    "- We must look up a value by using the index columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nd2[false, 'A']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Since `NDSparse` acts like an array, we can look up all the rows where `x` is `false`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-d NDSparse with 2 values (Float64):\n",
       "y   │\n",
       "────┼────\n",
       "'A' │ 0.2\n",
       "'B' │ 0.1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nd2[false, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Iteration\n",
    "\n",
    "- `Table` and `NDSparse` also differ in how they iterate over the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(x = false, y = 'A', z = 0.2)\n",
      "(x = false, y = 'B', z = 0.1)\n",
      "(x = true, y = 'A', z = 0.4)\n",
      "(x = true, y = 'B', z = 0.3)\n"
     ]
    }
   ],
   "source": [
    "# Table: iterate over Tuples/NamedTuples of rows\n",
    "\n",
    "for row in t3\n",
    "    println(row)\n",
    "end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.2\n",
      "0.1\n",
      "0.4\n",
      "0.3\n"
     ]
    }
   ],
   "source": [
    "# NDSparse: iterate over values\n",
    "\n",
    "for item in nd2\n",
    "    println(item)\n",
    "end"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Loading tables from CSVs\n",
    "\n",
    "- Now we'll load data from multiple CSV files into a table.\n",
    "- Our example data is 8 different stocks' OHLC data (each stock in a separate file).\n",
    "    - We can start by looking at all the available files in the `stocksample` directory.\n",
    "    - `;` allows us to run shell commands from Jupyter or a Julia REPL.\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "aapl.us.txt\n",
      "amzn.us.txt\n",
      "dis.us.txt\n",
      "googl.us.txt\n",
      "ibm.us.txt\n",
      "msft.us.txt\n",
      "nflx.us.txt\n",
      "tsla.us.txt\n"
     ]
    }
   ],
   "source": [
    ";ls stocksample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Each txt file has the structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date,Open,High,Low,Close,Volume,OpenInt\r\n",
      "1984-09-07,0.42388,0.42902,0.41874,0.42388,23220030,0\r\n",
      "1984-09-10,0.42388,0.42516,0.41366,0.42134,18022532,0\r\n",
      "1984-09-11,0.42516,0.43668,0.42516,0.42902,42498199,0\r\n",
      "1984-09-12,0.42902,0.43157,0.41618,0.41618,37125801,0\r\n",
      "1984-09-13,0.43927,0.44052,0.43927,0.43927,57822062,0\r\n",
      "1984-09-14,0.44052,0.45589,0.44052,0.44566,68847968,0\r\n",
      "1984-09-17,0.45718,0.46357,0.45718,0.45718,53755262,0\r\n",
      "1984-09-18,0.45718,0.46103,0.44052,0.44052,27136886,0\r\n",
      "1984-09-19,0.44052,0.44566,0.43157,0.43157,29641922,0\r\n"
     ]
    }
   ],
   "source": [
    ";head stocksample/aapl.us.txt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- We can now use the `loadtable` function to load all files in the `stocksample` directory into one table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 56023 rows, 7 columns:\n",
       "Date        Open     High     Low      Close    Volume    OpenInt\n",
       "─────────────────────────────────────────────────────────────────\n",
       "1984-09-07  0.42388  0.42902  0.41874  0.42388  23220030  0\n",
       "1984-09-10  0.42388  0.42516  0.41366  0.42134  18022532  0\n",
       "1984-09-11  0.42516  0.43668  0.42516  0.42902  42498199  0\n",
       "1984-09-12  0.42902  0.43157  0.41618  0.41618  37125801  0\n",
       "1984-09-13  0.43927  0.44052  0.43927  0.43927  57822062  0\n",
       "1984-09-14  0.44052  0.45589  0.44052  0.44566  68847968  0\n",
       "1984-09-17  0.45718  0.46357  0.45718  0.45718  53755262  0\n",
       "1984-09-18  0.45718  0.46103  0.44052  0.44052  27136886  0\n",
       "1984-09-19  0.44052  0.44566  0.43157  0.43157  29641922  0\n",
       "1984-09-20  0.43286  0.43668  0.43286  0.43286  18453585  0\n",
       "1984-09-21  0.43286  0.44566  0.42388  0.42902  27842780  0\n",
       "1984-09-24  0.42902  0.43157  0.42516  0.42516  22033109  0\n",
       "⋮\n",
       "2017-10-27  319.75   324.59   316.66   320.87   6970118   0\n",
       "2017-10-30  319.18   323.78   317.25   320.08   4254378   0\n",
       "2017-10-31  320.23   331.95   320.18   331.53   5672347   0\n",
       "2017-11-01  332.25   332.609  320.26   321.08   8457336   0\n",
       "2017-11-02  300.13   308.69   292.63   299.26   19791416  0\n",
       "2017-11-03  299.5    306.25   295.13   306.09   8893974   0\n",
       "2017-11-06  307.0    307.5    299.01   302.78   6482486   0\n",
       "2017-11-07  301.02   306.5    300.03   306.05   5286320   0\n",
       "2017-11-08  305.5    306.89   301.3    304.31   4725510   0\n",
       "2017-11-09  302.5    304.46   296.3    302.99   5440335   0\n",
       "2017-11-10  302.5    308.36   301.85   302.99   4621912   0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks = loadtable(\"stocksample\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- All data from the 8 files is now in one table, but we don't have the stock ticker information!\n",
    "- `loadtable` has a `filenamecol` option that will add a column for us, which we will call `:Ticker`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 56023 rows, 8 columns:\n",
       "Columns:\n",
       "\u001b[1m#  \u001b[22m\u001b[1mcolname  \u001b[22m\u001b[1mtype\u001b[22m\n",
       "───────────────────\n",
       "1  Ticker   String\n",
       "2  Date     Date\n",
       "3  Open     Float64\n",
       "4  High     Float64\n",
       "5  Low      Float64\n",
       "6  Close    Float64\n",
       "7  Volume   Int64\n",
       "8  OpenInt  Int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks = loadtable(\"stocksample\"; filenamecol = :Ticker)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Furthermore, we can use the `indexcols` to specify how data is sorted.\n",
    "  - First we'll sort by `:Ticker`\n",
    "  - Then sort by `:Date`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 56023 rows, 8 columns:\n",
       "Columns:\n",
       "\u001b[1m#  \u001b[22m\u001b[1mcolname  \u001b[22m\u001b[1mtype\u001b[22m\n",
       "───────────────────\n",
       "1  Ticker   String\n",
       "2  Date     Date\n",
       "3  Open     Float64\n",
       "4  High     Float64\n",
       "5  Low      Float64\n",
       "6  Close    Float64\n",
       "7  Volume   Int64\n",
       "8  OpenInt  Int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks = loadtable(\"stocksample\"; filenamecol = :Ticker, indexcols = [:Ticker, :Date])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Notice the printing style has changed.  A summary is printed when the display width is too narrow to print all the columns.  In Jupyter, the width is hardcoded as 80 characters, so the table actually fits in this case.  We can override this behavior with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 56023 rows, 8 columns:\n",
       "\u001b[1mTicker         \u001b[22m\u001b[1mDate        \u001b[22mOpen     High     Low      Close    Volume    OpenInt\n",
       "────────────────────────────────────────────────────────────────────────────────\n",
       "\"aapl.us.txt\"  1984-09-07  0.42388  0.42902  0.41874  0.42388  23220030  0\n",
       "\"aapl.us.txt\"  1984-09-10  0.42388  0.42516  0.41366  0.42134  18022532  0\n",
       "\"aapl.us.txt\"  1984-09-11  0.42516  0.43668  0.42516  0.42902  42498199  0\n",
       "\"aapl.us.txt\"  1984-09-12  0.42902  0.43157  0.41618  0.41618  37125801  0\n",
       "\"aapl.us.txt\"  1984-09-13  0.43927  0.44052  0.43927  0.43927  57822062  0\n",
       "\"aapl.us.txt\"  1984-09-14  0.44052  0.45589  0.44052  0.44566  68847968  0\n",
       "\"aapl.us.txt\"  1984-09-17  0.45718  0.46357  0.45718  0.45718  53755262  0\n",
       "\"aapl.us.txt\"  1984-09-18  0.45718  0.46103  0.44052  0.44052  27136886  0\n",
       "\"aapl.us.txt\"  1984-09-19  0.44052  0.44566  0.43157  0.43157  29641922  0\n",
       "\"aapl.us.txt\"  1984-09-20  0.43286  0.43668  0.43286  0.43286  18453585  0\n",
       "\"aapl.us.txt\"  1984-09-21  0.43286  0.44566  0.42388  0.42902  27842780  0\n",
       "\"aapl.us.txt\"  1984-09-24  0.42902  0.43157  0.42516  0.42516  22033109  0\n",
       "⋮\n",
       "\"tsla.us.txt\"  2017-10-27  319.75   324.59   316.66   320.87   6970118   0\n",
       "\"tsla.us.txt\"  2017-10-30  319.18   323.78   317.25   320.08   4254378   0\n",
       "\"tsla.us.txt\"  2017-10-31  320.23   331.95   320.18   331.53   5672347   0\n",
       "\"tsla.us.txt\"  2017-11-01  332.25   332.609  320.26   321.08   8457336   0\n",
       "\"tsla.us.txt\"  2017-11-02  300.13   308.69   292.63   299.26   19791416  0\n",
       "\"tsla.us.txt\"  2017-11-03  299.5    306.25   295.13   306.09   8893974   0\n",
       "\"tsla.us.txt\"  2017-11-06  307.0    307.5    299.01   302.78   6482486   0\n",
       "\"tsla.us.txt\"  2017-11-07  301.02   306.5    300.03   306.05   5286320   0\n",
       "\"tsla.us.txt\"  2017-11-08  305.5    306.89   301.3    304.31   4725510   0\n",
       "\"tsla.us.txt\"  2017-11-09  302.5    304.46   296.3    302.99   5440335   0\n",
       "\"tsla.us.txt\"  2017-11-10  302.5    308.36   301.85   302.99   4621912   0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "IndexedTables.set_show_compact!(false)\n",
    "\n",
    "stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Motivation for ND Sparse\n",
    "\n",
    "### What was Apple's closing price on 1986-02-10?\n",
    "\n",
    "- For a `Table`, this requires a query\n",
    "- With `NDSparse`, this is just `getindex`\n",
    "    - Load data as `NDSparse` and use arbitrary indexes (`String` and `Date`) to look up the closing price:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.38164"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load data as NDSparse:\n",
    "\n",
    "stocksnd = loadndsparse(\"stocksample\", filenamecol=:Ticker, indexcols = [:Ticker, :Date])\n",
    "\n",
    "# Get the value associated with Apple and 1986-02-10:\n",
    "\n",
    "stocksnd[\"aapl.us.txt\", Date(1986, 2, 10)].Close"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving Tables to Disk\n",
    "\n",
    "- **JuliaDB** can save tables into a binary format that can be loaded efficiently in future Julia sessions.\n",
    "    - `save(table, destination)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 56023 rows, 8 columns:\n",
       "\u001b[1mTicker         \u001b[22m\u001b[1mDate        \u001b[22mOpen     High     Low      Close    Volume    OpenInt\n",
       "────────────────────────────────────────────────────────────────────────────────\n",
       "\"aapl.us.txt\"  1984-09-07  0.42388  0.42902  0.41874  0.42388  23220030  0\n",
       "\"aapl.us.txt\"  1984-09-10  0.42388  0.42516  0.41366  0.42134  18022532  0\n",
       "\"aapl.us.txt\"  1984-09-11  0.42516  0.43668  0.42516  0.42902  42498199  0\n",
       "\"aapl.us.txt\"  1984-09-12  0.42902  0.43157  0.41618  0.41618  37125801  0\n",
       "\"aapl.us.txt\"  1984-09-13  0.43927  0.44052  0.43927  0.43927  57822062  0\n",
       "\"aapl.us.txt\"  1984-09-14  0.44052  0.45589  0.44052  0.44566  68847968  0\n",
       "\"aapl.us.txt\"  1984-09-17  0.45718  0.46357  0.45718  0.45718  53755262  0\n",
       "\"aapl.us.txt\"  1984-09-18  0.45718  0.46103  0.44052  0.44052  27136886  0\n",
       "\"aapl.us.txt\"  1984-09-19  0.44052  0.44566  0.43157  0.43157  29641922  0\n",
       "\"aapl.us.txt\"  1984-09-20  0.43286  0.43668  0.43286  0.43286  18453585  0\n",
       "\"aapl.us.txt\"  1984-09-21  0.43286  0.44566  0.42388  0.42902  27842780  0\n",
       "\"aapl.us.txt\"  1984-09-24  0.42902  0.43157  0.42516  0.42516  22033109  0\n",
       "⋮\n",
       "\"tsla.us.txt\"  2017-10-27  319.75   324.59   316.66   320.87   6970118   0\n",
       "\"tsla.us.txt\"  2017-10-30  319.18   323.78   317.25   320.08   4254378   0\n",
       "\"tsla.us.txt\"  2017-10-31  320.23   331.95   320.18   331.53   5672347   0\n",
       "\"tsla.us.txt\"  2017-11-01  332.25   332.609  320.26   321.08   8457336   0\n",
       "\"tsla.us.txt\"  2017-11-02  300.13   308.69   292.63   299.26   19791416  0\n",
       "\"tsla.us.txt\"  2017-11-03  299.5    306.25   295.13   306.09   8893974   0\n",
       "\"tsla.us.txt\"  2017-11-06  307.0    307.5    299.01   302.78   6482486   0\n",
       "\"tsla.us.txt\"  2017-11-07  301.02   306.5    300.03   306.05   5286320   0\n",
       "\"tsla.us.txt\"  2017-11-08  305.5    306.89   301.3    304.31   4725510   0\n",
       "\"tsla.us.txt\"  2017-11-09  302.5    304.46   296.3    302.99   5440335   0\n",
       "\"tsla.us.txt\"  2017-11-10  302.5    308.36   301.85   302.99   4621912   0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "save(stocks, \"stocks.jdb\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reloading a Saved Table\n",
    "\n",
    "- To load a saved table, we use the `load` function rather than `loadtable`.\n",
    "- This is typically **much** faster than reloading from the CSVs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  0.025132 seconds (279.94 k allocations: 6.434 MiB)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Table with 56023 rows, 8 columns:\n",
       "\u001b[1mTicker         \u001b[22m\u001b[1mDate        \u001b[22mOpen     High     Low      Close    Volume    OpenInt\n",
       "────────────────────────────────────────────────────────────────────────────────\n",
       "\"aapl.us.txt\"  1984-09-07  0.42388  0.42902  0.41874  0.42388  23220030  0\n",
       "\"aapl.us.txt\"  1984-09-10  0.42388  0.42516  0.41366  0.42134  18022532  0\n",
       "\"aapl.us.txt\"  1984-09-11  0.42516  0.43668  0.42516  0.42902  42498199  0\n",
       "\"aapl.us.txt\"  1984-09-12  0.42902  0.43157  0.41618  0.41618  37125801  0\n",
       "\"aapl.us.txt\"  1984-09-13  0.43927  0.44052  0.43927  0.43927  57822062  0\n",
       "\"aapl.us.txt\"  1984-09-14  0.44052  0.45589  0.44052  0.44566  68847968  0\n",
       "\"aapl.us.txt\"  1984-09-17  0.45718  0.46357  0.45718  0.45718  53755262  0\n",
       "\"aapl.us.txt\"  1984-09-18  0.45718  0.46103  0.44052  0.44052  27136886  0\n",
       "\"aapl.us.txt\"  1984-09-19  0.44052  0.44566  0.43157  0.43157  29641922  0\n",
       "\"aapl.us.txt\"  1984-09-20  0.43286  0.43668  0.43286  0.43286  18453585  0\n",
       "\"aapl.us.txt\"  1984-09-21  0.43286  0.44566  0.42388  0.42902  27842780  0\n",
       "\"aapl.us.txt\"  1984-09-24  0.42902  0.43157  0.42516  0.42516  22033109  0\n",
       "⋮\n",
       "\"tsla.us.txt\"  2017-10-27  319.75   324.59   316.66   320.87   6970118   0\n",
       "\"tsla.us.txt\"  2017-10-30  319.18   323.78   317.25   320.08   4254378   0\n",
       "\"tsla.us.txt\"  2017-10-31  320.23   331.95   320.18   331.53   5672347   0\n",
       "\"tsla.us.txt\"  2017-11-01  332.25   332.609  320.26   321.08   8457336   0\n",
       "\"tsla.us.txt\"  2017-11-02  300.13   308.69   292.63   299.26   19791416  0\n",
       "\"tsla.us.txt\"  2017-11-03  299.5    306.25   295.13   306.09   8893974   0\n",
       "\"tsla.us.txt\"  2017-11-06  307.0    307.5    299.01   302.78   6482486   0\n",
       "\"tsla.us.txt\"  2017-11-07  301.02   306.5    300.03   306.05   5286320   0\n",
       "\"tsla.us.txt\"  2017-11-08  305.5    306.89   301.3    304.31   4725510   0\n",
       "\"tsla.us.txt\"  2017-11-09  302.5    304.46   296.3    302.99   5440335   0\n",
       "\"tsla.us.txt\"  2017-11-10  302.5    308.36   301.85   302.99   4621912   0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@time reloaded_stocks = load(\"stocks\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "true"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks == reloaded_stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Using  Selectors\n",
    "\n",
    "### Selectors are powerful ways to select and manipulate data\n",
    "\n",
    "1. `Integer`: column at position\n",
    "2. `Symbol`: column by name\n",
    "3. `Array`: itself\n",
    "4. `Pair{Selection => Function}`: function mapped to selection\n",
    "5. `Tuple` of selections: table of each selection\n",
    "\n",
    "### Selectors show up in many places (everything in green)\n",
    "\n",
    "<code>select(t, <span style=\"color: green\">which</span>)\n",
    "map(f, t; <span style=\"color: green\">select</span>)\n",
    "reduce(f, t; <span style=\"color: green\">select</span>)\n",
    "filter(f, t; <span style=\"color: green\">select</span>)\n",
    "groupby(f, t, <span style=\"color: green\">by</span>; <span style=\"color: green\">select</span>)\n",
    "groupreduce(f, t, <span style=\"color: green\">by</span>; <span style=\"color: green\">select</span>)\n",
    "join(f, l, r; how, <span style=\"color: green\">lkey</span>, <span style=\"color: green\">rkey</span>, <span style=\"color: green\">lselect</span>, <span style=\"color: green\">rselect</span>)\n",
    "groupjoin(f, l, r; how, <span style=\"color: green\">lkey</span>, <span style=\"color: green\">rkey</span>, <span style=\"color: green\">lselect</span>, <span style=\"color: green\">rselect</span>)\n",
    "</code>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selector Examples\n",
    "\n",
    "- A single selection returns a Vector:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{String,1}:\n",
       " \"aapl.us.txt\"\n",
       " \"aapl.us.txt\"\n",
       " \"aapl.us.txt\"\n",
       " \"aapl.us.txt\"\n",
       " \"aapl.us.txt\""
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(stocks, 1)[1:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{Date,1}:\n",
       " 1984-09-07\n",
       " 1984-09-10\n",
       " 1984-09-11\n",
       " 1984-09-12\n",
       " 1984-09-13"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(stocks, :Date)[1:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Multiple selections return a table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table with 5 rows, 2 columns:\n",
       "\u001b[1mTicker         \u001b[22m\u001b[1mDate\u001b[22m\n",
       "─────────────────────────\n",
       "\"aapl.us.txt\"  1984-09-07\n",
       "\"aapl.us.txt\"  1984-09-10\n",
       "\"aapl.us.txt\"  1984-09-11\n",
       "\"aapl.us.txt\"  1984-09-12\n",
       "\"aapl.us.txt\"  1984-09-13"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select(stocks, (1, :Date))[1:5]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 0.6.2-pre",
   "language": "julia",
   "name": "julia-0.6"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "0.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
